Imports Microsoft.VisualBasic
Imports System.Data

Namespace LookUp
    Public Class FillLookUp
        Enum eLookUpType
            other = 0
            broker = 1
            provider = 2
            prdt = 3
            fund = 4
            prem_rider = 5
            country = 6
            country_city = 7
            frequency = 8
        End Enum

        Private LookupType As eLookUpType
        Private LookUpCode As String = ""
        Private ParentCode As String = ""
        Private DropDownList As DropDownList

        Public Function FillDropDown(ByVal Combobox As DropDownList, _
                                    ByVal FillType As eLookUpType, _
                                    Optional ByVal FillCode As String = "", _
                                    Optional ByVal ParentCd As String = "") As Boolean

            Dim blnSuccess As Boolean = False


            LookupType = FillType
            LookUpCode = FillCode
            ParentCode = ParentCd
            DropDownList = Combobox

            Select Case LookupType
                Case eLookUpType.other
                    blnSuccess = FillOthers()
                Case eLookUpType.provider, _
                    eLookUpType.broker, _
                    eLookUpType.country, _
                    eLookUpType.frequency

                    blnSuccess = FillCombo()

                Case eLookUpType.prdt, _
                    eLookUpType.fund, _
                    eLookUpType.prem_rider, _
                    eLookUpType.country, _
                    eLookUpType.country_city

                    blnSuccess = FillWithParent()



            End Select

            Return blnSuccess

        End Function

        'Others
        Private Function FillOthers() As Boolean
            Dim dm As New DAOMain.CommonDAO
            Dim blnSuccess As Boolean = False
            Dim lFunc As New PolyFunc.ASPFunc

            Try
                Dim dt As DataTable = dm.ExecQuery("Select Lookup_seq as ID, Lookup_Desc as [Desc] From lookup_mstr" & _
                                                               " Where Lookup_cd = " & dm.Q(LookUpCode) & _
                                                               " Order by Lookup_Desc")

                lFunc.BindDropDown(DropDownList, dt, "Desc", "ID")
                'With DropDownList
                '    .DataSource = dt
                '    .DataValueField = "ID"
                '    .DataTextField = "Desc"
                '    .DataBind()
                'End With
                blnSuccess = True
            Catch ex As Exception

            End Try

            dm = Nothing

            Return blnSuccess
            lFunc = Nothing
        End Function

        'provider and Countre
        Private Function FillCombo() As Boolean
            Dim dm As New DAOMain.CommonDAO
            Dim blnSuccess As Boolean = False
            Dim strFieldCD As String = ""
            Dim strFieldDesc As String = ""
            Dim strTable As String = ""
            Dim lfunc As New PolyFunc.ASPFunc

            Select Case LookupType
                Case eLookUpType.broker
                    strFieldCD = "broker_cd"
                    strFieldDesc = "broker_Desc"
                    strTable = "broker_mstr"
                Case eLookUpType.provider
                    strFieldCD = "provider_cd"
                    strFieldDesc = "provider_Desc"
                    strTable = "provider_mstr"
                Case eLookUpType.country
                    strFieldCD = "country_cd"
                    strFieldDesc = "country_desc"
                    strTable = "country_mstr"
                Case eLookUpType.frequency
                    strFieldCD = "frequency_cd"
                    strFieldDesc = "frequency_desc"
                    strTable = "frequency_mstr"
            End Select
            Try
                Dim dt As DataTable = dm.ExecQuery("Select " & strFieldCD & " as ID, " & strFieldDesc & " as [Desc] " & _
                                                    " From " & strTable & _
                                                    " Order by " & strFieldDesc)

                lfunc.BindDropDown(DropDownList, dt, "Desc", "ID")
                'With DropDownList
                '    .DataSource = dt
                '    .DataValueField = "ID"
                '    .DataTextField = "Desc"
                '    .DataBind()
                'End With
                blnSuccess = True
            Catch ex As Exception

            End Try

            dm = Nothing
            lfunc = Nothing

            Return blnSuccess
        End Function

        'With Parent
        Private Function FillWithParent() As Boolean
            Dim dm As New DAOMain.CommonDAO
            Dim blnSuccess As Boolean = False
            Dim strFieldCD As String = ""
            Dim strFieldDesc As String = ""
            Dim strTable As String = ""
            Dim strParentField As String = ""

            Select Case LookupType
                Case eLookUpType.provider
                    strFieldCD = "provider_cd"
                    strFieldDesc = "provider_desc"
                    strTable = "provider_mstr"
                    strParentField = "broker_cd"
                Case eLookUpType.prdt
                    strFieldCD = "prdt_cd"
                    strFieldDesc = "prdt_desc"
                    strTable = "prdt_mstr"
                    strParentField = "provider_cd"
                Case eLookUpType.fund
                    strFieldCD = "fund_cd"
                    strFieldDesc = "fund_desc"
                    strTable = "fund_mstr"
                    strParentField = "prdt_cd"
                Case eLookUpType.prem_rider
                    strFieldCD = ""
                    strFieldDesc = ""
                    strTable = "prem_rider_mstr"
                    strParentField = "prdt_cd"
                
                Case eLookUpType.country_city
                    strFieldCD = "city_cd"
                    strFieldDesc = "city_desc"
                    strTable = "country_city_dtl"
                    strParentField = "country_cd"
            End Select

            Try
                Dim dt As DataTable = dm.ExecQuery("Select " & strFieldCD & " as ID," & _
                                                    " " & strFieldDesc & " as [Desc]" & _
                                                    " From " & strTable & _
                                                    " Where " & strParentField & " = " & dm.Q(ParentCode) & _
                                                    " Order by " & strFieldDesc)

                Dim lfunc As New PolyFunc.ASPFunc

                lfunc.BindDropDown(DropDownList, dt, "Desc", "ID")
                'With DropDownList
                '    .DataSource = dt
                '    .DataValueField = "ID"
                '    .DataTextField = "Desc"
                '    .DataBind()
                'End With

                lfunc = Nothing
                blnSuccess = True
            Catch ex As Exception

            End Try

            dm = Nothing

            Return blnSuccess
        End Function

    End Class

    Public Class General_Lookup
        Public LookUpCode As String = ""

        Public Function LookUpSelected(ByVal strSelected As String, ByVal objFieldLabel As Label, _
              ByVal objCountLabel As Label, ByVal objAddBtn As LinkButton, ByVal objDGrid As DataGrid)

            Select Case strSelected
                Case "Advisor Levels"
                    objFieldLabel.Text = "Advisor Level:"
                    objCountLabel.Text = "Advisor Levels: "
                    objAddBtn.Text = "Add New Level"
                    objDGrid.Columns(1).HeaderText = "Advisor Level"

                Case "Beneficiary Ranking"
                    objFieldLabel.Text = "Beneficiary Ranking:"
                    objCountLabel.Text = "Total Ranks: "
                    objAddBtn.Text = "Add New Rank"
                    objDGrid.Columns(1).HeaderText = "Beneficiary Ranking"

                    'Case "Cities"
                    '  objFieldLabel.Text = "City Name:"
                    '  objCountLabel.Text = "Total Cities: "
                    '  objAddBtn.Text = "Add New City"
                    '  objDGrid.Columns(1).HeaderText = "City"

                Case "Client Types"
                    objFieldLabel.Text = "Client Type:"
                    objCountLabel.Text = "Total Types: "
                    objAddBtn.Text = "Add New Type"
                    objDGrid.Columns(1).HeaderText = "Client Type"

                Case "Correspondence Status"
                    objFieldLabel.Text = "Correspondence Status:"
                    objCountLabel.Text = "Total Status: "
                    objAddBtn.Text = "Add New Status"
                    objDGrid.Columns(1).HeaderText = "Correspondence Status"

                Case "Commission Status"
                    objFieldLabel.Text = "Commission Status:"
                    objCountLabel.Text = "Total Status: "
                    objAddBtn.Text = "Add New Status"
                    objDGrid.Columns(1).HeaderText = "Commission Status"

                Case "Commission Type"
                    objFieldLabel.Text = "Commission Type:"
                    objCountLabel.Text = "Total Type: "
                    objAddBtn.Text = "Add New Type"
                    objDGrid.Columns(1).HeaderText = "Commission Type"

                Case "Marital Status"
                    objFieldLabel.Text = "Marital Status:"
                    objCountLabel.Text = "Total Marital Status: "
                    objAddBtn.Text = "Add New Marital Status"
                    objDGrid.Columns(1).HeaderText = "Marital Status"

                    'Case "Countries"
                    '  objFieldLabel.Text = "Country:"
                    '  objCountLabel.Text = "Total Countries: "
                    '  objAddBtn.Text = "Add New Country"
                    '  objDGrid.Columns(1).HeaderText = "Country"

                Case "Departments"
                    objFieldLabel.Text = "Department:"
                    objCountLabel.Text = "Total Departments: "
                    objAddBtn.Text = "Add New Department"
                    objDGrid.Columns(1).HeaderText = "Department"

                Case "Forms of Payment"
                    objFieldLabel.Text = "Form of Payment:"
                    objCountLabel.Text = "Total Forms: "
                    objAddBtn.Text = "Add New Form"
                    objDGrid.Columns(1).HeaderText = "Form of Payment"

                Case "Frequency of Payment"
                    objFieldLabel.Text = "Frequency of Payment:"
                    objCountLabel.Text = "Total Frequencies: "
                    objAddBtn.Text = "Add New Frequency"
                    objDGrid.Columns(1).HeaderText = "Frequency of Payment"

                Case "Fund Currency"
                    objFieldLabel.Text = "Fund Currency:"
                    objCountLabel.Text = "Total Currencies: "
                    objAddBtn.Text = "Add New Currency"
                    objDGrid.Columns(1).HeaderText = "Fund Currency"

                Case "ID Types"
                    objFieldLabel.Text = "ID Type:"
                    objCountLabel.Text = "Total Types: "
                    objAddBtn.Text = "Add New Type"
                    objDGrid.Columns(1).HeaderText = "ID Type"

                Case "Issue Status"
                    objFieldLabel.Text = "Issue Status:"
                    objCountLabel.Text = "Total Status: "
                    objAddBtn.Text = "Add New Status"
                    objDGrid.Columns(1).HeaderText = "Issue Status"

                Case "Medical Rating"
                    objFieldLabel.Text = "Medical Rating:"
                    objCountLabel.Text = "Total Ratings: "
                    objAddBtn.Text = "Add New Rating"
                    objDGrid.Columns(1).HeaderText = "Medical Rating"

                Case "Nationalities"
                    objFieldLabel.Text = "Nationality:"
                    objCountLabel.Text = "Total Nationalities: "
                    objAddBtn.Text = "Add New Nationality"
                    objDGrid.Columns(1).HeaderText = "Nationality"

                Case "Payment Status"
                    objFieldLabel.Text = "Payment Status:"
                    objCountLabel.Text = "Total Status: "
                    objAddBtn.Text = "Add New Status"
                    objDGrid.Columns(1).HeaderText = "Payment Status"

                Case "Plan Types"
                    objFieldLabel.Text = "Plan Type:"
                    objCountLabel.Text = "Total Types: "
                    objAddBtn.Text = "Add New Type"
                    objDGrid.Columns(1).HeaderText = "Plan Type"

                Case "Policy Status"
                    objFieldLabel.Text = "Policy Status:"
                    objCountLabel.Text = "Total Status: "
                    objAddBtn.Text = "Add New Status"
                    objDGrid.Columns(1).HeaderText = "Policy Status"

                Case "Relationship"
                    objFieldLabel.Text = "Relationship:"
                    objCountLabel.Text = "Total Relationships: "
                    objAddBtn.Text = "Add New Relationship"
                    objDGrid.Columns(1).HeaderText = "Relationship"

                Case "Smoking Status"
                    objFieldLabel.Text = "Smoking Status:"
                    objCountLabel.Text = "Total Status: "
                    objAddBtn.Text = "Add New Status"
                    objDGrid.Columns(1).HeaderText = "Smoking Status"

                Case "Source of Funds"
                    objFieldLabel.Text = "Source of Fund:"
                    objCountLabel.Text = "Total Sources: "
                    objAddBtn.Text = "Add New Source"
                    objDGrid.Columns(1).HeaderText = "Source of Fund"

            End Select

            Return ""
        End Function

    Public Function DataGridFill(ByVal objDGrid As DataGrid, ByVal objLabel As Label, Optional ByVal param1 As String = "", _
            Optional ByVal param2 As String = "") As Boolean

      Dim dm As New DAOMain.CommonDAO
      Dim blnSuccess As Boolean = False
      Dim sDS As New Data.DataSet
      Dim sConn As New Data.SqlClient.SqlConnection
      Dim sCom As New Data.SqlClient.SqlCommand
      Dim sDApt As New Data.SqlClient.SqlDataAdapter(sCom)

      Select Case LookUpCode
        Case "BROKER"
          sCom.CommandText = "SELECT * FROM broker_mstr"

        Case "BROKER_CONTACT"
          sCom.CommandText = "SELECT broker_dtl_last_name + ', ' + broker_dtl_first_name + ' ' + " & _
            "broker_dtl_middle_name AS Name, broker_dtl_position, tel_no, mobile_no, " & _
            "email_address, broker_cd, broker_contact_cd FROM broker_contact_dtl WHERE broker_cd='" & _
            param1 & "'"

        Case "COUNTRY"
          sCom.CommandText = "SELECT * FROM country_mstr"

        Case "PROVIDER"
          sCom.CommandText = "SELECT provider_mstr.provider_cd, provider_mstr.provider_desc, " & _
            "broker_mstr.broker_desc, provider_mstr.broker_cd FROM provider_mstr, broker_mstr " & _
            "WHERE provider_mstr.broker_cd = broker_mstr.broker_cd " & _
            IIf(param1 <> "", "AND provider_mstr.broker_cd='" & param1 & "'", "")

        Case "PROVIDER_CONTACT"
          sCom.CommandText = "SELECT provider_dtl_last_name + ', ' + provider_dtl_first_name + ' ' + " & _
            "provider_dtl_middle_name AS Name, provider_dtl_position, tel_no, mobile_no, " & _
            "email_address, provider_cd, provider_contact_cd FROM provider_contact_dtl WHERE provider_cd='" & _
            param1 & "'"

        Case "PRODUCT"
          sCom.CommandText = "SELECT prdt_mstr.*, lookup_desc FROM prdt_mstr, lookup_mstr WHERE " & _
            "prdt_mstr.prdt_plan_type = lookup_mstr.lookup_seq AND lookup_mstr.lookup_cd='PLAN_TYPE' " & _
            IIf(param1 <> Nothing, "AND prdt_mstr.provider_cd='" & param1 & "'", "")

        Case "FUNDS"
          sCom.CommandText = "SELECT * FROM fund_mstr" & _
            IIf(param1 <> Nothing, " WHERE prdt_cd='" & param1 & "'", "")

        Case "COMMIS_RATE"
          sCom.CommandText = "SELECT L.Lookup_desc, U.advisor_level_cd, L.Lookup_seq, " & _
            "ISNULL(U.user_com_rate, 0) AS user_com_rate " & _
            "FROM (SELECT * FROM user_commission_rate_mstr WHERE (prdt_cd = N'" & param1 & "')) AS U " & _
            "RIGHT OUTER JOIN lookup_mstr AS L ON U.advisor_level_cd = L.Lookup_seq " & _
            "WHERE (L.Lookup_cd = N'ADVISOR_LEVEL') ORDER BY L.Lookup_desc"
          'sCom.CommandText = "SELECT U.*, L.lookup_desc FROM user_commission_rate_mstr U, lookup_mstr L " & _
          '  "WHERE (L.Lookup_cd = N'ADVISOR_LEVEL') AND (L.Lookup_seq = U.advisor_level_cd) AND " & _
          '  "(U.prdt_cd='" & param1 & "')"
          'sCom.CommandText = "SELECT * FROM user_commission_rate_mstr" & _
          ' IIf(param1 <> Nothing And param2 <> Nothing, " WHERE prdt_cd='" & param1 & "' AND advisor_level_cd='" & param2 & "'", "")

        Case "RIDERS"
          sCom.CommandText = "SELECT * FROM prem_rider_mstr" & _
            IIf(param1 <> Nothing, " WHERE prdt_cd='" & param1 & "'", "")

        Case "RISK"
          sCom.CommandText = "SELECT * FROM risk_profile_mstr"

        Case "RISK_DETAIL"
          sCom.CommandText = "SELECT * FROM risk_profile_dtl WHERE risk_profile_cd='" & param1 & "'"

        Case "CITIES"
          sCom.CommandText = "SELECT * FROM country_city_dtl" & _
            IIf(param1 <> Nothing, " WHERE country_cd='" & param1 & "'", "")

        Case "USER_SEARCH"
          'Code this?
          sCom.CommandText = "SELECT [user_name],[email_address],[user_last_name] + ', ' + [user_first_name] + ' ' + " & _
            "[user_middle_name] AS Name,D.[lookup_desc] AS Dept,[role_desc] AS Role " & param2 ' FROM [user_mstr], [role_mstr], [lookup_mstr] " & _
          '"WHERE [lookup_mstr].[lookup_cd]='DEPARTMENT' AND [role_mstr].[dept_cd]=[lookup_mstr].[lookup_seq] " & _
          '"AND [user_mstr].[role_cd]=[role_mstr].[role_cd] AND [user_mstr].[dept_cd]=[role_mstr].[dept_cd]"

        Case "ADD_USER"
          'sCom.CommandText = "SELECT * FROM country_mstr"

        Case "USER_ROLES"
          sCom.CommandText = "SELECT * FROM role_mstr" & _
            IIf(param1 <> Nothing, " WHERE dept_cd='" & param1 & "'", "")

        Case "MODULE_ACCESS"
          sCom.CommandText = "SELECT * FROM vw_role_dtl WHERE role_cd='" & param1 & "'"

        Case "FREQUENCY"
          sCom.CommandText = "SELECT * FROM frequency_mstr"

        Case Else
          sCom.CommandText = "Select Lookup_seq as ID, Lookup_Desc as [Desc] From lookup_mstr Where Lookup_cd = " & _
            dm.Q(LookUpCode)

      End Select

      Try
        'sConn.ConnectionString = "Data Source=HEAVEN;Initial Catalog=CIMS;Integrated Security=True"
        sConn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("CnnStr")
        sCom.CommandType = Data.CommandType.Text
        sCom.Connection = sConn
        sConn.Open()
        sDApt.Fill(sDS)

        objLabel.Text = sDS.Tables(0).Rows.Count

        With objDGrid
          .DataSource = sDS
          .DataBind()
        End With

        blnSuccess = True
      Catch ex As Exception

      Finally
        sConn.Close()
      End Try

      dm = Nothing

      Return blnSuccess
    End Function

    Public Function ExecQuery(ByVal strQ As String) As Boolean
      Dim blnSuccess As Boolean = False
      Dim sConn As New Data.SqlClient.SqlConnection
      Dim sCom As New Data.SqlClient.SqlCommand

      Try
        'sConn.ConnectionString = "Data Source=HEAVEN;Initial Catalog=CIMS;Integrated Security=True"
        sConn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("CnnStr")
        sCom.CommandType = Data.CommandType.Text
        sCom.Connection = sConn
        sCom.CommandText = strQ
        sConn.Open()
        sCom.ExecuteNonQuery()

        blnSuccess = True
      Catch ex As Exception

      Finally
        sConn.Close()
      End Try

      Return blnSuccess
    End Function

        Public Function MaxCode() As String
            Dim dm As New DAOMain.CommonDAO
            Dim sConn As New Data.SqlClient.SqlConnection
            Dim sCom As New Data.SqlClient.SqlCommand
            Dim sDApt As New Data.SqlClient.SqlDataAdapter(sCom)
            Dim sDT As New Data.DataTable
            Dim maxcount As Integer = 0

            Select Case LookUpCode
                Case "BROKER", "PROVIDER", "COUNTRY", "RISK_PROFILE", "ROLE"
                    sCom.CommandText = "Select Max(convert(numeric, isnull(" & LookUpCode & "_cd, 0))) as ID FROM " & LookUpCode & "_mstr"

                Case "RISK_PROFILE_DTL"
                    sCom.CommandText = "Select Max(convert(numeric, isnull(risk_dtl_cd, 0))) as ID FROM " & LookUpCode

                Case "BROKER_CONTACT", "PROVIDER_CONTACT"
                    sCom.CommandText = "Select Max(convert(numeric, isnull(" & LookUpCode & "_cd, 0))) as ID FROM " & LookUpCode & "_dtl"

                Case "CITIES"
                    sCom.CommandText = "Select Max(convert(numeric, isnull(city_cd, 0))) as ID FROM country_city_dtl"

                Case "FREQUENCY"
                    sCom.CommandText = "Select Max(convert(numeric, isnull(frequency_cd, 0))) as ID FROM frequency_mstr"

                Case "USER_ROLES"
                    sCom.CommandText = "Select Max(convert(numeric, isnull(role_cd, 0))) as ID FROM role_mstr"

                Case Else
                    sCom.CommandText = "Select Max(convert(numeric, isnull(Lookup_seq, 0))) as ID From lookup_mstr"

            End Select

            Try
        'sConn.ConnectionString = "User ID=blue;Password=2blue;Initial Catalog=cims;Data Source=OEMKID"
                sConn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("CnnStr")
                sCom.CommandType = Data.CommandType.Text
                sCom.Connection = sConn
                sConn.Open()
                sDApt.Fill(sDT)

                maxcount = CInt(sDT.Rows(0).Item(0).ToString) + 1
            Catch ex As Exception
                maxcount = 1
            Finally
                sConn.Close()
            End Try

            Return CStr(maxcount)
        End Function

        Public Function LookUpDecode(ByVal strSelected As String)

            Select Case strSelected
                Case "Advisor Levels"
                    LookUpCode = "ADVISOR_LEVEL"

                Case "Beneficiary Ranking"
                    LookUpCode = "BENEFICIARY_RANK"

                Case "Cities"
                    LookUpCode = "CITY"

                Case "Client Types"
                    LookUpCode = "CLIENT_TYPE"

                Case "Correspondence Status"
                    LookUpCode = "CORRES_STAT"

                Case "Commission Status"
                    LookUpCode = "COMMIS_STAT"

                Case "Commission Type"
                    LookUpCode = "COMMIS_TYPE"

                Case "Countries"
                    LookUpCode = "COUNTRY"

                Case "Departments"
                    LookUpCode = "DEPARTMENT"

                Case "Forms of Payment"
                    LookUpCode = "PAYMENT_FORMS"

                Case "Frequency of Payment"
                    LookUpCode = "PAYMENT_FREQUENCY"

                Case "Fund Currency"
                    LookUpCode = "CURRENCY"

                Case "ID Types"
                    LookUpCode = "ID_TYPE"

                Case "Issue Status"
                    LookUpCode = "ISSUE_STATUS"

                Case "Marital Status"
                    LookUpCode = "MARITAL_STATUS"

                Case "Medical Rating"
                    LookUpCode = "MEDICAL_RATING"

                Case "Nationalities"
                    LookUpCode = "NATIONALITY"

                Case "Payment Status"
                    LookUpCode = "PAYMENT_STATUS"

                Case "Plan Types"
                    LookUpCode = "PLAN_TYPE"

                Case "Policy Status"
                    LookUpCode = "POLICY_STATUS"

                Case "Relationship"
                    LookUpCode = "RELEATIONSHIP"

                Case "Smoking Status"
                    LookUpCode = "SMOKING_STATUS"

                Case "Source of Funds"
                    LookUpCode = "SOURCE_FUND"

            End Select

            Return ""
        End Function

        Public Function OKtoDELETE(ByVal cd As String) As Boolean
            'Dim dm As New DAOMain.CommonDAO
            Dim blnSuccess As Boolean = True

            Dim sDT As New Data.DataTable
            Dim sConn As New Data.SqlClient.SqlConnection
            Dim sCom As New Data.SqlClient.SqlCommand
            Dim sDApt As New Data.SqlClient.SqlDataAdapter(sCom)

            sCom.CommandType = Data.CommandType.Text

            Select Case LookUpCode
                Case "BROKER"
                    sCom.CommandText = "SELECT * FROM provider_mstr WHERE broker_cd='" & cd & "'"

                Case "PROVIDER"
                    sCom.CommandText = "SELECT * FROM prdt_mstr WHERE provider_cd='" & cd & "'"

                Case "PRODUCT"
                    sCom.CommandText = "SELECT * FROM prdt_mstr WHERE prdt_cd='" & cd & "'"

                Case "RISK"
                    sCom.CommandText = "SELECT [risk_profile_cd] FROM [policy_client_risk] " & _
                      "WHERE [risk_profile_cd]='" & cd & "' " & _
                      "UNION " & _
                      "SELECT [risk_profile_cd] FROM [policy_insured_risk] " & _
                      "WHERE [risk_profile_cd]='" & cd & "' " & _
                      "UNION " & _
                      "SELECT [risk_profile_cd] FROM [risk_profile_dtl] " & _
                      "WHERE [risk_profile_cd]='" & cd & "'"

                Case "RISK_DETAIL"
                    sCom.CommandText = "SELECT [risk_dtl_cd] FROM [policy_client_risk] " & _
                      "WHERE [risk_dtl_cd]='" & cd & "' UNION " & _
                      "SELECT [risk_dtl_cd] FROM [policy_insured_risk] " & _
                      "WHERE [risk_dtl_cd]='" & cd & "'"

                Case "FUNDS"
                    sCom.CommandText = "SELECT * FROM policy_fund_dtl WHERE fund_cd='" & cd & "'"

                Case "COUNTRY"
                    sCom.CommandText = "SELECT * FROM country_city_dtl WHERE country_cd='" & cd & "'"

                Case "CITIES"
                    sCom.CommandText = "SELECT [city_cd] FROM [policy_bene_dtl] " & _
                      "WHERE [city_cd]='" & cd & "' " & _
                      "UNION " & _
                      "SELECT [city_cd] FROM [policy_client_corres] " & _
                      "WHERE [city_cd]='" & cd & "' " & _
                      "UNION " & _
                      "SELECT [city_cd] FROM [policy_client_dtl] " & _
                      "WHERE [city_cd]='" & cd & "' " & _
                      "UNION " & _
                      "SELECT [city_cd] FROM [policy_trustees_dtl] " & _
                      "WHERE [city_cd]='" & cd & "' " & _
                      "UNION " & _
                      "SELECT [city_cd] FROM [user_mstr] " & _
                      "WHERE [city_cd]='" & cd & "' " & _
                      "UNION " & _
                      "SELECT [city_cd] FROM [policy_insured_dtl] " & _
                      "WHERE [city_cd]='" & cd & "'"

                Case "USER_SEARCH"
                    'code this
                    sCom.CommandText = "sp_check_user_data"
                    Dim tParam As New SqlClient.SqlParameter

                    tParam.ParameterName = "@username"
                    tParam.Value = cd
                    sCom.Parameters.Add(tParam)
                    sCom.CommandType = Data.CommandType.StoredProcedure
                    tParam = Nothing

                Case "USER_ROLES"
                    sCom.CommandText = "Select * From user_mstr where role_cd = '" & cd & "'"
                    'sCom.CommandText = "SELECT * FROM prdt_mstr WHERE prdt_cd='" & cd & "'"

                Case "FREQUENCY"
                    'sCom.CommandText = "Select * From user_mstr where role_cd = '" & cd & "'"

                Case Else
                    ' sCom.CommandText = "Select Lookup_seq as ID, Lookup_Desc as [Desc] From lookup_mstr Where Lookup_cd = " & _
                    '   dm.Q(LookUpCode)

            End Select

            Try
                sConn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("CnnStr")

                sCom.Connection = sConn
                sConn.Open()
                sDApt.Fill(sDT)

                If sDT.Rows.Count > 0 Then
                    blnSuccess = False
                    Exit Function
                End If

                blnSuccess = True
            Catch ex As Exception

            Finally
                sConn.Close()
            End Try

            'dm = Nothing

            Return blnSuccess
        End Function

    Public Function Loader(ByVal objD As Object, ByVal frmSTR As String, Optional ByVal strText As String = "", _
              Optional ByVal strValue As String = "", Optional ByVal param1 As String = "", _
              Optional ByVal param2 As String = "", Optional ByVal param3 As String = "") As Boolean

      'Dim dm As New DAOMain.CommonDAO
      Dim blnSuccess As Boolean = False
      Dim sDS As New Data.DataSet
      Dim sConn As New Data.SqlClient.SqlConnection
      Dim sCom As New Data.SqlClient.SqlCommand
      Dim sDApt As New Data.SqlClient.SqlDataAdapter(sCom)

      Select Case frmSTR
        Case "vw_policy_adv_dtl"
          sCom.CommandText = "SELECT * FROM " & frmSTR

        Case "Currency_of_Policy"
          sCom.CommandText = "SELECT Lookup_desc FROM lookup_mstr WHERE Lookup_cd = N'CURRENCY'"

        Case "Frequency_of_Payment"
          sCom.CommandText = "SELECT * FROM frequency_mstr"


        Case "BROKER", "COUNTRY"
          sCom.CommandText = "SELECT * FROM " & frmSTR & "_mstr"

        Case "CITY"
          sCom.CommandText = "SELECT * FROM country_city_dtl WHERE country_cd='" & param1 & "'"

        Case "PROVIDER"
          sCom.CommandText = "SELECT * FROM provider_mstr WHERE broker_cd='" & param1 & "'"

        Case "PRODUCT"
          sCom.CommandText = "SELECT * FROM prdt_mstr WHERE provider_cd='" & param1 & "'"

        Case "PLAN_TYPE", "CURRENCY", "DEPARTMENT", "ADVISOR_LEVEL"
          sCom.CommandText = "Select Lookup_seq as ID, Lookup_Desc as [Desc] From lookup_mstr " & _
            "Where Lookup_cd='" & frmSTR & "'"

        Case "ROLE"
          sCom.CommandText = "SELECT * FROM role_mstr WHERE dept_cd='" & param1 & "'"

        Case "AGENTS"
          sCom.CommandText = "SELECT user_name, user_last_name + ', ' + user_first_name + ' ' + user_middle_name AS advisor_name " & _
            "FROM dbo.user_mstr WHERE (advisor_tag = -1)"

        Case "POLICY"
          sCom.CommandText = "SELECT policy_no FROM policy_trans"

        Case "POLICY_STATUS"
          sCom.CommandText = "SELECT Lookup_desc FROM lookup_mstr WHERE Lookup_cd = N'POLICY_STATUS'"

        Case "PROCESS_STATUS"
          sCom.CommandText = "SELECT Lookup_desc FROM lookup_mstr WHERE Lookup_cd = N'PROCESS_STATUS'"

        Case "PAYMENT_STATUS"
          sCom.CommandText = "SELECT Lookup_desc FROM lookup_mstr WHERE Lookup_cd = N'PAYMENT_STATUS'"

        Case "GENDER"
          sCom.CommandText = "SELECT Lookup_desc FROM lookup_mstr WHERE Lookup_cd = N'GENDER'"

        Case "MARITAL_STATUS"
          sCom.CommandText = "SELECT Lookup_desc FROM lookup_mstr WHERE Lookup_cd = N'MARITAL_STATUS'"

        Case Else
          'sCom.CommandText = "Select Lookup_seq as ID, Lookup_Desc as [Desc] From lookup_mstr Where Lookup_cd = " & _
          ' dm.Q(LookUpCode)

      End Select

      Try
        'sConn.ConnectionString = "Data Source=HEAVEN;Initial Catalog=CIMS;Integrated Security=True"
        sConn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("CnnStr")
        sCom.CommandType = Data.CommandType.Text
        sCom.Connection = sConn
        sConn.Open()
        sDApt.Fill(sDS)

        With objD
          .DataSource = sDS
          If strText <> "" Then .DataTextField = strText
          If strValue <> "" Then .DataValueField = strValue
          .DataBind()
        End With

        blnSuccess = True
      Catch ex As Exception

      Finally
        sConn.Close()
      End Try

      'dm = Nothing

      Return blnSuccess
    End Function

        Public Function DuplicateCode(ByVal index As String) As Boolean
            Dim sConn As New Data.SqlClient.SqlConnection
            Dim sCom As New Data.SqlClient.SqlCommand
            Dim sDApt As New Data.SqlClient.SqlDataAdapter(sCom)
            Dim sDT As New Data.DataTable
            Dim boolOK As Boolean = True

            Select Case LookUpCode
                Case "PRODUCT"
          sCom.CommandText = "Select * FROM prdt_mstr WHERE prdt_cd='" & index & "'"

                Case "ADD_USER"
                    sCom.CommandText = "Select * FROM user_mstr WHERE user_name='" & index & "'"

                Case "FUND"
                    sCom.CommandText = "Select * FROM fund_mstr WHERE fund_cd='" & index & "'"

                Case "RIDER"
                    sCom.CommandText = "Select * FROM prem_rider_mstr WHERE prem_rider_cd='" & index & "'"

                Case "FREQUENCY"
                    sCom.CommandText = "Select * FROM frequency_mstr WHERE frequency_cd='" & index & "'"
            End Select

            Try
                'sConn.ConnectionString = "User ID=blue;Password=2blue;Initial Catalog=cims;Data Source=OEMKID"
                sConn.ConnectionString = System.Configuration.ConfigurationManager.AppSettings("CnnStr")
                sCom.CommandType = Data.CommandType.Text
                sCom.Connection = sConn
                sConn.Open()
                sDApt.Fill(sDT)

                If sDT.Rows.Count > 0 Then boolOK = False

            Catch ex As Exception
                boolOK = False
            Finally
                sConn.Close()
            End Try

            Return boolOK
        End Function

    End Class
End Namespace

